package com.trace.demo.repository;

import com.trace.demo.entity.TraceQueryLog;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;
import org.springframework.stereotype.Repository;

import java.time.LocalDateTime;
import java.util.List;
import java.util.Map;

/**
 * 溯源查询日志数据访问接口
 */
@Repository
public interface TraceQueryLogRepository extends JpaRepository<TraceQueryLog, Long> {

    /**
     * 统计总查询次数
     * @return 查询次数
     */
    long count();

    /**
     * 统计特定日期后的查询次数 (使用scan_time字段)
     * @param dateTime 日期时间
     * @return 查询次数
     */
    long countByScanTimeAfter(LocalDateTime dateTime);
    
    /**
     * 统计特定日期后的查询次数 (使用query_time字段)
     * @param dateTime 日期时间
     * @return 查询次数
     */
    long countByQueryTimeAfter(LocalDateTime dateTime);

    /**
     * 按设备类型分组统计
     * @return 设备类型分布统计
     */
    @Query("SELECT tql.deviceType as name, COUNT(tql) as value FROM TraceQueryLog tql GROUP BY tql.deviceType")
    List<Map<String, Object>> countByDeviceType();

    /**
     * 按地理位置分组统计
     * @return 地理位置分布统计
     */
    @Query("SELECT tql.location as name, COUNT(tql) as value FROM TraceQueryLog tql WHERE tql.location IS NOT NULL GROUP BY tql.location ORDER BY COUNT(tql) DESC")
    List<Map<String, Object>> countByLocation();

    /**
     * 获取最常查询的产品
     * @param limit 限制数量
     * @return 产品查询排行
     */
    @Query(value = "SELECT tql.product_name as productName, tql.batch_number as batchNumber, " +
            "COUNT(tql.id) as scanCount, " +
            "ROUND(COUNT(tql.id) * 100.0 / (SELECT COUNT(*) FROM trace_query_logs), 1) as ratio " +
            "FROM trace_query_logs tql " +
            "WHERE tql.product_name IS NOT NULL " +
            "GROUP BY tql.product_name, tql.batch_number " +
            "ORDER BY COUNT(tql.id) DESC " +
            "LIMIT :limit", nativeQuery = true)
    List<Map<String, Object>> findTopProductsByQueryCount(@Param("limit") int limit);

    /**
     * 获取指定日期内每天的查询趋势 (使用scan_time字段)
     * @param startDate 开始日期
     * @param endDate 结束日期
     * @return 日期和查询次数
     */
    @Query(value = "SELECT DATE_FORMAT(tql.scan_time, '%m-%d') as date, COUNT(tql.id) as count " +
            "FROM trace_query_logs tql " +
            "WHERE tql.scan_time BETWEEN :startDate AND :endDate " +
            "GROUP BY DATE_FORMAT(tql.scan_time, '%m-%d') " +
            "ORDER BY DATE(tql.scan_time)", nativeQuery = true)
    List<Map<String, Object>> countDailyTrend(@Param("startDate") LocalDateTime startDate, @Param("endDate") LocalDateTime endDate);

    /**
     * 获取指定月份内每月的查询趋势 (使用scan_time字段)
     * @param startDate 开始日期
     * @param endDate 结束日期
     * @return 月份和查询次数
     */
    @Query(value = "SELECT DATE_FORMAT(tql.scan_time, '%Y-%m') as date, COUNT(tql.id) as count " +
            "FROM trace_query_logs tql " +
            "WHERE tql.scan_time BETWEEN :startDate AND :endDate " +
            "GROUP BY DATE_FORMAT(tql.scan_time, '%Y-%m') " +
            "ORDER BY DATE_FORMAT(tql.scan_time, '%Y-%m')", nativeQuery = true)
    List<Map<String, Object>> countMonthlyTrend(@Param("startDate") LocalDateTime startDate, @Param("endDate") LocalDateTime endDate);

    /**
     * 分页查询所有日志 (按scan_time字段排序)
     * @param pageable 分页参数
     * @return 分页结果
     */
    Page<TraceQueryLog> findAllByOrderByScanTimeDesc(Pageable pageable);
    
    /**
     * 分页查询所有日志 (按query_time字段排序)
     * @param pageable 分页参数
     * @return 分页结果
     */
    Page<TraceQueryLog> findAllByOrderByQueryTimeDesc(Pageable pageable);

    /**
     * 统计指定时间范围内的查询记录数 (使用query_time字段)
     * @param startTime 开始时间
     * @param endTime 结束时间
     * @return 记录数
     */
    long countByQueryTimeBetween(LocalDateTime startTime, LocalDateTime endTime);
    
    /**
     * 统计指定时间范围内的查询记录数 (使用scan_time字段)
     * @param startTime 开始时间
     * @param endTime 结束时间
     * @return 记录数
     */
    long countByScanTimeBetween(LocalDateTime startTime, LocalDateTime endTime);

    /**
     * 查询地区分布统计 (使用query_location字段)
     * @return 地区统计数据
     */
    @Query("SELECT tql.queryLocation, COUNT(tql.id) FROM TraceQueryLog tql WHERE tql.queryLocation IS NOT NULL GROUP BY tql.queryLocation ORDER BY COUNT(tql.id) DESC")
    List<Object[]> findQueryLocationStats();
    
    /**
     * 查询地区分布统计 (使用location字段)
     * @return 地区统计数据
     */
    @Query("SELECT tql.location, COUNT(tql.id) FROM TraceQueryLog tql WHERE tql.location IS NOT NULL GROUP BY tql.location ORDER BY COUNT(tql.id) DESC")
    List<Object[]> findLocationStats();

    /**
     * 查询产品分布统计
     * @param limit 限制数量
     * @return 产品统计数据
     */
    @Query(value = "SELECT tc.product_id, COUNT(tql.id) FROM trace_query_logs tql " +
            "LEFT JOIN trace_codes tc ON tql.trace_code = tc.code OR tql.trace_code = tc.trace_code " +
            "WHERE tc.product_id IS NOT NULL " +
            "GROUP BY tc.product_id " +
            "ORDER BY COUNT(tql.id) DESC " +
            "LIMIT ?1", nativeQuery = true)
    List<Object[]> findProductQueryStats(int limit);

    /**
     * 查询设备类型分布统计 (使用query_device字段)
     * @return 设备类型统计数据
     */
    @Query("SELECT tql.queryDevice, COUNT(tql.id) FROM TraceQueryLog tql WHERE tql.queryDevice IS NOT NULL GROUP BY tql.queryDevice ORDER BY COUNT(tql.id) DESC")
    List<Object[]> findDeviceTypeStats();
    
    /**
     * 查询设备类型分布统计 (使用device_type字段)
     * @return 设备类型统计数据
     */
    @Query("SELECT tql.deviceType, COUNT(tql.id) FROM TraceQueryLog tql WHERE tql.deviceType IS NOT NULL GROUP BY tql.deviceType ORDER BY COUNT(tql.id) DESC")
    List<Object[]> findDeviceStats();
    
    /**
     * 查询浏览器分布统计
     * @return 浏览器统计数据
     */
    @Query("SELECT tql.browser, COUNT(tql.id) FROM TraceQueryLog tql WHERE tql.browser IS NOT NULL GROUP BY tql.browser ORDER BY COUNT(tql.id) DESC")
    List<Object[]> findBrowserStats();
    
    /**
     * 查询操作系统分布统计
     * @return 操作系统统计数据
     */
    @Query("SELECT tql.os, COUNT(tql.id) FROM TraceQueryLog tql WHERE tql.os IS NOT NULL GROUP BY tql.os ORDER BY COUNT(tql.id) DESC")
    List<Object[]> findOsStats();
    
    /**
     * 统计特定溯源码的查询次数
     * @param code 溯源码
     * @param traceCode 溯源码（别名）
     * @return 查询次数
     */
    @Query("SELECT COUNT(tql) FROM TraceQueryLog tql WHERE tql.code = :code OR tql.traceCode = :traceCode")
    long countByCodeOrTraceCode(@Param("code") String code, @Param("traceCode") String traceCode);
    
    /**
     * 统计特定溯源码在指定日期之后的查询次数 (使用scan_time字段)
     * @param code 溯源码
     * @param dateTime 日期时间
     * @return 查询次数
     */
    @Query("SELECT COUNT(tql) FROM TraceQueryLog tql WHERE tql.code = :code AND tql.scanTime > :dateTime")
    long countByCodeAndScanTimeAfter(@Param("code") String code, @Param("dateTime") LocalDateTime dateTime);
    
    /**
     * 统计特定溯源码在指定日期之后的查询次数 (使用query_time字段)
     * @param code 溯源码
     * @param dateTime 日期时间
     * @return 查询次数
     */
    @Query("SELECT COUNT(tql) FROM TraceQueryLog tql WHERE tql.code = :code AND tql.queryTime > :dateTime")
    long countByCodeAndQueryTimeAfter(@Param("code") String code, @Param("dateTime") LocalDateTime dateTime);
    
    /**
     * 查询特定溯源码的查询记录 (按scan_time字段排序)
     * @param code 溯源码
     * @param pageable 分页参数
     * @return 查询记录
     */
    @Query("SELECT tql FROM TraceQueryLog tql WHERE tql.code = :code ORDER BY tql.scanTime DESC")
    List<TraceQueryLog> findByCodeOrderByScanTimeDesc(@Param("code") String code, Pageable pageable);
    
    /**
     * 查询特定溯源码的查询记录 (按query_time字段排序)
     * @param code 溯源码
     * @param pageable 分页参数
     * @return 查询记录
     */
    @Query("SELECT tql FROM TraceQueryLog tql WHERE tql.code = :code ORDER BY tql.queryTime DESC")
    List<TraceQueryLog> findByCodeOrderByQueryTimeDesc(@Param("code") String code, Pageable pageable);
} 